package com.x.crm.dao;

import com.x.crm.model.CustomerShop;
import com.x.crm.model.Intention;
import com.x.crm.model.IntentionModel;
import com.x.framework.dao.BaseDao;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

@Repository
public class IntentionModelDao extends BaseDao{
    private static final String baseQueryStr = "SELECT * FROM CRM_INTENTION_MODEL WHERE INTENTION_ID = ?";
    public List<IntentionModel> selectByIntentionId(IntentionModel intentionModelWhere) throws Exception {
        List<Object> params = new ArrayList<Object>();
        params.add(intentionModelWhere.getIntentionId());
        return this.queryForList(baseQueryStr.toString(), IntentionModel.class, params);
    }

    private static final String baseQueryIntentionCustomerSql = "SELECT  T1.CAR_ID, T2.CUSTOMER_SHOP_ID, T3.CUSTOMER_NICKNAME, T3.CUSTOMER_HEAD_IMAGE,T2.CUSTOMER_MOBILE,T2.CEATE_TIME, T3.CUSTOMER_REMARK, T4.CUSTOMER_SHOP_ID, T4.CUSTOMER_REMARK  FROM CRM_INTENTION_MODEL T1, CRM_INTENTION T2, CRM_CUSTOMER T3, CRM_CUSTOMER_SHOP T4  WHERE T1.INTENTION_ID = T2.INTENTION_ID AND  T2.CUSTOMER_SHOP_ID = T4.CUSTOMER_SHOP_ID  AND T3.CUSTOMER_ID = T4.CUSTOMER_ID";
    public List<IntentionModel> selectIntentionCustomerList(IntentionModel intentionModel) throws Exception {
        StringBuilder sql = new StringBuilder(baseQueryIntentionCustomerSql);
        List<Object> params = new ArrayList<Object>();
        if(this.isNotNull(intentionModel.getCarId())){
            sql.append(" and T1.CAR_ID = ? ");
            params.add(intentionModel.getCarId());
        }
        if(this.isNotNull(intentionModel.getSeriesId())){
            sql.append(" and T1.SERIES_ID = ? ");
            params.add(intentionModel.getSeriesId());
        }
        Intention intention = intentionModel.getIntention();
        if(intention != null) {
            if (this.isNotNull(intention.getMarketId())) {
                sql.append(" and T2.MARKET_ID = ? ");
                params.add(intention.getMarketId());
            }
            if (this.isNotNull(intention.getShopId())) {
                sql.append(" and T2.SHOP_ID = ? ");
                params.add(intention.getShopId());
            }
            if (this.isNotNull(intention.getCustomerShopId())) {
                sql.append(" and T2.CUSTOMER_SHOP_ID = ? ");
                params.add(intention.getCustomerShopId());
            }
            if (this.isNotNull(intention.getCustomerMobile())) {
                sql.append(" and T2.CUSTOMER_MOBILE = ? ");
                params.add(intention.getCustomerMobile());
            }
        }
        return this.queryForList(sql.toString(), IntentionModel.class, params ,intentionModel);
    }

    private static final String baseQueryStr_selectIntentionModelList = "SELECT * FROM CRM_INTENTION_MODEL IM WHERE IM.INTENTION_MODEL_ID > 0 ";
    public List<IntentionModel> selectIntentionModelList(IntentionModel where) throws Exception {
        StringBuilder sql = new StringBuilder(baseQueryStr_selectIntentionModelList);
        List<Object> params = new ArrayList<Object>();
        basicSearchCondition(where,sql,params);
        return this.queryForList(sql.toString(), IntentionModel.class, params, where);
    }



    private static final String baseQueryStr_selectIntentionModelNum = "SELECT COUNT(INTENTION_MODEL_ID) FROM CRM_INTENTION_MODEL IM WHERE IM.INTENTION_MODEL_ID > 0 ";
    public int selectIntentionModelNum(IntentionModel intentionModel) throws Exception {
        StringBuilder sql = new StringBuilder(baseQueryStr_selectIntentionModelNum);
        List<Object> params = new ArrayList<Object>();
        basicSearchCondition(intentionModel,sql,params);
        return this.queryForInt(sql.toString(), params);
    }


    private void basicSearchCondition(IntentionModel intentionModel, StringBuilder sql, List<Object> params) {
        if(this.isNotNull(intentionModel.getBrandId())){
            sql.append(" and im.brand_id = ? ");
            params.add(intentionModel.getBrandId());
        }
        if(this.isNotNull(intentionModel.getIntentionId())){
            sql.append(" and im.intention_id = ? ");
            params.add(intentionModel.getIntentionId());
        }
        if(this.isNotNull(intentionModel.getIntentionModelId())){
            sql.append(" and im.intention_model_id = ? ");
            params.add(intentionModel.getIntentionModelId());
        }
        if(this.isNotNull(intentionModel.getSeriesId())){
            sql.append(" and im.series_id = ? ");
            params.add(intentionModel.getSeriesId());
        }
        if(this.isNotNull(intentionModel.getCarId())){
            sql.append(" and im.car_id = ? ");
            params.add(intentionModel.getCarId());
        }
    }



    private static final String baseQueryGroupSeries = "SELECT * FROM CRM_INTENTION_MODEL WHERE INTENTION_ID = ? GROUP BY SERIES_ID,BRAND_ID";
    public List<IntentionModel> selectGroupBySeriesList(IntentionModel intentionModel) throws Exception {
        List<Object> params = new ArrayList<Object>();
        params.add(intentionModel.getIntentionId());
        return this.queryForList(baseQueryGroupSeries.toString(), IntentionModel.class, params);
    }


    private static final String baseQueryModelCustomerSql = "SELECT  T1.INTENTION_MODEL_ID, T1.INTENTION_ID, T1.CAR_ID, T1.SERIES_NAME,T1.BRAND_NAME,T1.CAR_TITLE, T1.CAR_IMAGE_URL, T1.CAR_RETAIL_PRICE FROM CRM_INTENTION_MODEL T1, CRM_INTENTION T2, CRM_CUSTOMER_SHOP T3, CRM_CUSTOMER T4  WHERE T1.INTENTION_ID = T2.INTENTION_ID AND  T2.CUSTOMER_SHOP_ID = T3.CUSTOMER_SHOP_ID AND T3.CUSTOMER_ID = T4.CUSTOMER_ID  ";
    public List<IntentionModel> selectModelList(IntentionModel intentionModel, CustomerShop customerShop) throws Exception {
        StringBuilder sql = new StringBuilder(baseQueryModelCustomerSql);
        List<Object> params = new ArrayList<Object>();
        if(this.isNotNull(intentionModel.getCarId())){
            sql.append(" and T1.CAR_ID = ? ");
            params.add(intentionModel.getCarId());
        }
        if(this.isNotNull(intentionModel.getSeriesId())){
            sql.append(" and T1.SERIES_ID = ? ");
            params.add(intentionModel.getSeriesId());
        }
        if(this.isNotNull(intentionModel.getBrandId())){
            sql.append(" and T1.BRAND_ID = ? ");
            params.add(intentionModel.getBrandId());
        }
        if(this.isNotNull(customerShop.getMarketId())){
            sql.append(" and T3.MARKET_ID = ? ");
            params.add(customerShop.getMarketId());
        }
        if(this.isNotNull(customerShop.getShopId())){
            sql.append(" and T3.SHOP_ID = ? ");
            params.add(customerShop.getShopId());
        }
        if(this.isNotNull(customerShop.getCustomerShopId())){
            sql.append(" and T3.CUSTOMER_SHOP_ID = ? ");
            params.add(customerShop.getCustomerShopId());
        }
        sql.append("ORDER BY T1.INTENTION_MODEL_ID DESC ");
        return this.queryForList(sql.toString(), IntentionModel.class, params ,intentionModel);
    }
}
